const pool = require('../config/mysql');

class BillModel {
    // 列表+分页+模糊
    static async fetchAll({
        accounting_month = '',
        record_date = '',
        accounting_item = '',
        record_type = '',
        page = 1,
        pagesize = 10
    }) {
        const off = (page - 1) * pagesize;

        let where = ` WHERE 1 = 1 `;
        let paramsArr = [];

        // 模糊搜索备注
        if (accounting_month) {
            where += ` AND accounting_month LIKE ? `;
            paramsArr.push(`%${accounting_month}%`);
        }

        // 模糊匹配记账日期
        if (record_date) {
            where += ` AND record_date LIKE ? `;
            paramsArr.push(`%${record_date}%`);
        }

        // 模糊匹配收支项目
        if (accounting_item) {
            where += ` AND accounting_item LIKE ? `;
            paramsArr.push(`%${accounting_item}%`);
        }

        // 模糊匹配记录类型
        if (record_type) {
            where += ` AND record_type LIKE ? `;
            paramsArr.push(`%${record_type}%`);
        }

        try {
            // 查询数据列表 - 修改排序方式为按记账日期降序
            const [rows] = await pool.query(
                `SELECT *
       FROM financial_bill
       ${where}
       ORDER BY record_date DESC, id DESC  -- 按记账日期降序，同一天按ID降序
       LIMIT ?, ?`,
                [...paramsArr, Number(off), Number(pagesize)]
            );

            // 查询总数
            const [totalRows] = await pool.query(
                `SELECT COUNT(*) AS total
       FROM financial_bill
       ${where}`,
                paramsArr
            );

            return { rows, total: totalRows[0].total };
        } catch (error) {
            console.error('数据库查询错误:', error.message);
            console.error('错误堆栈:', error.stack);
            throw error;
        }
    }

    // 根据ID获取账单详情
    static async findById(id) {
        try {
            const [rows] = await pool.query(
                'SELECT * FROM financial_bill WHERE id = ?',
                [id]
            );
            return rows[0];
        } catch (error) {
            console.error('数据库查询错误:', error.message);
            console.error('错误堆栈:', error.stack);
            throw error;
        }
    }

    // 添加账单
    static async create(billData) {
        try {
            const {
                record_date,
                accounting_month,
                record_type,
                accounting_item,
                amount,
                handler,
                related_document,
                receipt_image_path,
                remarks
            } = billData;

            const [result] = await pool.query(


                `INSERT INTO financial_bill 
                (record_date, accounting_month, record_type, accounting_item, amount, handler, related_document, receipt_image_path, remarks) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
                [
                    record_date,
                    accounting_month,
                    record_type,
                    accounting_item,
                    amount,
                    handler,
                    related_document || null,
                    receipt_image_path || null,
                    remarks || null
                ]
            );

            return result.insertId;
        } catch (error) {
            console.error('数据库插入错误:', error.message);
            console.error('错误堆栈:', error.stack);
            throw error;
        }
    }

    // 更新账单
    static async update(id, billData) {
        try {
            // 构建动态SQL查询
            const fields = [];
            const values = [];

            // 定义允许更新的字段
            const allowedFields = [
                'record_date',
                'accounting_month',
                'record_type',
                'accounting_item',
                'amount',
                'handler',
                'related_document',
                'receipt_image_path',
                'remarks'
            ];

            // 只处理传入的并且是允许更新的字段
            Object.keys(billData).forEach(key => {
                if (allowedFields.includes(key)) {
                    fields.push(`${key} = ?`);
                    // 对于可选字段，如果值为undefined或null，设置为null
                    values.push(billData[key] || null);
                }
            });

            // 如果没有要更新的字段，则直接返回
            if (fields.length === 0) {
                return true; // 没有字段需要更新，视为成功
            }

            // 添加ID到values数组的末尾
            values.push(id);

            const [result] = await pool.query(
                `UPDATE financial_bill SET ${fields.join(', ')} WHERE id = ?`,
                values
            );

            return result.affectedRows > 0;
        } catch (error) {
            console.error('数据库更新错误:', error.message);
            console.error('错误堆栈:', error.stack);
            throw error;
        }
    }
}

module.exports = BillModel;